﻿/******************************************************************************
'Description:装试生产准备看板类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhunan
'create Date:2019.7.23
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class ZSWorkPrepareLookBoardBusiness
    {
        private uMESReportOracleDAL dal = new uMESReportOracleDAL();

        public DataTable GetQiTaoLvOld()
        {
            string sql = @"
                            SELECT m.mfgorderid,m.mfgordername,ep.containerno,ep.containerid,pf.productfamilyname
                            FROM enginepartplan ep
                            INNER JOIN mfgorder m ON ep.mfgorderid = m.mfgorderid
                            LEFT JOIN productbase pb ON pb.productbaseid = m.productbaseid
                            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,m.productid)
                            LEFT JOIN Workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
                            LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
                            LEFT JOIN containerinstore cst ON cst.aufnr = m.mfgordername
                            LEFT JOIN Workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
                            LEFT JOIN bom b ON b.workflowid = ep.workflowid
                            LEFT JOIN billtype bt ON bt.billtypeid = b.billtypeid
                            WHERE ep.containerno IS NOT NULL AND wfs.sequence > 5 AND bt.billtypename = '大小件目录' AND cst.aufnr IS NULL AND ep.type = 1 ";

            DataTable dt1 = dal.GetDataTable(sql);

            string strsql = @"
                                SELECT pm.containerno,pm.productid,pm.firstassembleqty,NVL(ims.issueqty,0) issueqty
                                FROM partmenuinfo pm
                                LEFT JOIN (
                                             SELECT im.containerno,im.partmenuinfoid,SUM(NVL(im.issueqty,0)) issueqty
                                             FROM issueerpmaterial im
                                             GROUP BY im.containerno,im.partmenuinfoid
                                          ) ims ON pm.partmenuinfoid = ims.partmenuinfoid
                                WHERE pm.parttype = 0 AND pm.firstassembleqty > NVL(ims.issueqty,0) ";
            DataTable dt2 = dal.GetDataTable(strsql);

            DataTable dtPieDataResult = new DataTable();
            dtPieDataResult.Columns.Add("productfamilyname");
            dtPieDataResult.Columns.Add("mfgorderid");
            dtPieDataResult.Columns.Add("quejianqty",typeof(decimal));
            dtPieDataResult.Columns.Add("qitaoqty", typeof(decimal));

            DataTable dtMachines = dt1.DefaultView.ToTable(true, "mfgorderid", "productfamilyname");

            DataRow drPie = dtPieDataResult.NewRow();
            foreach (DataRow dr in dtMachines.Rows)
            {
                drPie = dtPieDataResult.NewRow();
                string strMfgOrderid = dr["mfgorderid"].ToString();
                DataTable dtMachine = dt1.Select(string.Format("mfgorderid = '{0}'",strMfgOrderid)).CopyToDataTable();

                if (dtMachine.Select("containerid is null").Length > 0)
                {
                    drPie["mfgorderid"] = strMfgOrderid;
                    drPie["productfamilyname"] = dr["productfamilyname"].ToString();
                    drPie["quejianqty"] = 1;
                    drPie["qitaoqty"] = 0;
                    dtPieDataResult.Rows.Add(drPie);
                    continue;
                }

                bool isQiTao = false;

                foreach (DataRow drContainerNo in dtMachine.Rows)
                {
                    isQiTao = false;
                    string strContainerNo = drContainerNo["containerno"].ToString();
                    DataRow[] drs = dt2.Select(string.Format("containerno = '{0}'",strContainerNo));
                    if (drs.Length > 0)
                    {
                        break;
                    }
                }

                if (isQiTao)
                {
                    drPie["mfgorderid"] = strMfgOrderid;
                    drPie["productfamilyname"] = dr["productfamilyname"].ToString();
                    drPie["quejianqty"] = 0;
                    drPie["qitaoqty"] = 1;
                    dtPieDataResult.Rows.Add(drPie);
                }
                else
                {
                    drPie["mfgorderid"] = strMfgOrderid;
                    drPie["productfamilyname"] = dr["productfamilyname"].ToString();
                    drPie["quejianqty"] = 1;
                    drPie["qitaoqty"] = 0;
                    dtPieDataResult.Rows.Add(drPie);
                }
            }

            dtPieDataResult.AcceptChanges();

            if (dtPieDataResult.Rows.Count > 0)
            {
                DataTable dtProductFamily = dtPieDataResult.DefaultView.ToTable(true, "productfamilyname");
                dtProductFamily.Columns.Add("quejianqty");
                dtProductFamily.Columns.Add("qitaoqty");
                dtProductFamily.Columns.Add("qitaolv");

                foreach (DataRow drPF in dtProductFamily.Rows)
                {
                    string filter = string.Format("productfamilyname = '{0}'", drPF["productfamilyname"].ToString());

                    decimal qitaoqty = Convert.ToDecimal(dtPieDataResult.Compute("sum(qitaoqty)", filter));
                    decimal quejianqty = Convert.ToDecimal(dtPieDataResult.Compute("sum(quejianqty)", filter));

                    drPF["quejianqty"] = quejianqty;
                    drPF["qitaoqty"] = qitaoqty;
                    drPF["qitaolv"] = ((qitaoqty / (qitaoqty + quejianqty)) * 100).ToString("0.00");
                }

                return dtProductFamily;
            }
            else
            {
                return new DataTable();
            }

        }

        //齐套率
        public DataTable GetQiTaoLv()
        {
            string sql = @"
SELECT t.productfamilyname,t.attributevalue,CASE WHEN COUNT(1) = SUM(t.isQitao) THEN '齐套' ELSE '未齐套' END isQitao
FROM
(
    SELECT pf.productfamilyname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,
           SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN 1 ELSE 0 END isQitao
    FROM enginepartplan ep
    LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
    LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
    LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN containerinstore CS ON cs.Aufnr = m.mfgordername
    LEFT JOIN bom b ON b.workflowid = ep.workflowid
    LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
    LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
    LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
    --查机型
    LEFT JOIN productbase pbm1 ON pbm1.productbaseid = m.productbaseid
    LEFT JOIN product pm ON pm.productid = NVL(pbm1.revofrcdid,m.productid)
    LEFT JOIN productworkflowmapping pw ON pw.productid = pm.productid AND pw.workflowid = pm.workflowid
    LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid = pw.workflowid
    LEFT JOIN productfamily pf ON pf.productfamilyid = wff.productfamilylistid
    LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty FROM Collecterpmaterial cm WHERE cm.qty > 0 GROUP BY cm.mfgordername,cm.productname
              ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
    WHERE pl.Bigsmallpart = '大零件' AND cs.Aufnr IS NULL AND wfs.sequence < 5
    GROUP BY pf.productfamilyname,
          u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty
) t
GROUP BY t.productfamilyname,t.attributevalue
";
            DataTable dtQuery = dal.GetDataTable(sql);

            DataTable dtResult = dtQuery.DefaultView.ToTable(true, "productfamilyname");
            dtResult.Columns.Add("qitaoQty");
            dtResult.Columns.Add("notQitaoQty");
            dtResult.Columns.Add("qitaolv");

            foreach (DataRow dr in dtResult.Rows)
            {
                string qitaoFilter = string.Format(" productfamilyname = '{0}' and isQitao = '齐套'", dr["productfamilyname"].ToString());
                string notQitaoFilter = string.Format(" productfamilyname = '{0}' and isQitao = '未齐套'", dr["productfamilyname"].ToString());

                decimal qitaoQty = Convert.ToDecimal(dtQuery.Select(qitaoFilter).Length);
                decimal notQitaoQty = Convert.ToDecimal(dtQuery.Select(notQitaoFilter).Length);

                dr["qitaoQty"] = qitaoQty;
                dr["notQitaoQty"] = notQitaoQty;
                dr["qitaolv"] = ((qitaoQty / (qitaoQty + notQitaoQty)) * 100).ToString("0.00");
            }
            return dtResult;
        }

        //齐套率明细
        public DataTable GetQiTaoLvDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT t.productfamilyname,t.attributevalue,t.mfgordername,t.mfgorderid,t.productname,t.description,t.faliaoQty,t.Qtyrequired,t.status
FROM
(
SELECT pf.productfamilyname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,NVL(cmm.qty,0) faliaoQty,
       SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN '已发料' ELSE '未发料' END status
FROM enginepartplan ep
LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
LEFT JOIN containerinstore CS ON cs.Aufnr = m.mfgordername
LEFT JOIN bom b ON b.workflowid = ep.workflowid
LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
--查机型
LEFT JOIN productbase pbm1 ON pbm1.productbaseid = m.productbaseid
LEFT JOIN product pm ON pm.productid = NVL(pbm1.revofrcdid,m.productid)
LEFT JOIN productworkflowmapping pw ON pw.productid = pm.productid AND pw.workflowid = pm.workflowid
LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid = pw.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wff.productfamilylistid
LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty FROM Collecterpmaterial cm WHERE cm.qty > 0 GROUP BY cm.mfgordername,cm.productname
            ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
WHERE pl.Bigsmallpart = '大零件' AND cs.Aufnr IS NULL AND wfs.sequence < 5
";
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND pf.productfamilyname = '{0}'", para["productfamilyname"].ToString());
            }

            sql += @"
GROUP BY pf.productfamilyname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty
) t
WHERE t.status = '未发料'
ORDER BY t.attributevalue,t.productname
";
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //查询标准样件
        public DataTable GetBZYJData()
        {
            string sql = @"
                            SELECT t.productfamily,t.total,nvl(m.todual,0) dual
                            FROM
                            (
                              SELECT et.productfamily,COUNT(1) total
                              FROM etalonbasedata et
                              GROUP BY et.productfamily
                            ) t 
                            LEFT JOIN 
                            (
                               SELECT et.productfamily,COUNT(1) todual
                               FROM etalonbasedata et
                               WHERE et.finishdate IS NOT NULL AND finishdate - SYSDATE > 0 AND finishdate - SYSDATE < 60
                               GROUP BY et.productfamily
                            ) m ON m.productfamily = t.productfamily";


            return dal.GetDataTable(sql);
        }

        //标准样件--明细信息
        public DataTable GetBZYJDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            if (para["queryType"].ToString() == "total")
            {
                string sql = @"SELECT et.etalonbasedatano,et.productname,et.productdesc,et.productfamily,et.etaloncontent,et.stepinfo,et.begindate,et.finishdate,et.etalontype
                               FROM etalonbasedata et";
                if (para.ContainsKey("productfamily"))
                {
                    sql += string.Format(" WHERE et.productfamily = '{0}'", para["productfamily"].ToString());
                }
                uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
                pageCount = Convert.ToInt32(retR.PageCount);
                rowCount = Convert.ToInt32(retR.RowCount);

                return retR.DBTable;
            }
            else
            {
                string sql = @"SELECT et.etalonbasedatano,et.productname,et.productdesc,et.productfamily,et.etaloncontent,et.stepinfo,et.begindate,et.finishdate,et.etalontype
                               FROM etalonbasedata et
                               WHERE et.finishdate IS NOT NULL AND finishdate - SYSDATE > 0 AND (finishdate - SYSDATE) < 60 ";
                if (para.ContainsKey("productfamily"))
                {
                    sql += string.Format(" AND et.productfamily = '{0}'", para["productfamily"].ToString());
                }
                uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
                pageCount = Convert.ToInt32(retR.PageCount);
                rowCount = Convert.ToInt32(retR.RowCount);

                return retR.DBTable;
            }
        
        }

        //油封期预警
        public DataTable GetOilInfo(Dictionary<string, object> para)
        {
            string sql = @"
                        SELECT w.workcentername,nvl(m.delay,0) delay,nvl(m.ycq,0) ycq
                        FROM Workcenter w
                        LEFT JOIN
                        (
                            SELECT t.workcentername,SUM(CASE WHEN t.status = '超期' THEN 1 ELSE 0 END) delay,SUM(CASE WHEN t.status = '预超期' THEN 1 ELSE 0 END) ycq
                            FROM
                            (
                                SELECT w.workcentername,
                                       CASE WHEN m.yfCount > 1 AND el.createdate < m.maxCreatadate THEN '已再次油封'
                                            WHEN el.releasesealdate IS NOT NULL THEN '解封'
                                            WHEN el.releasesealdate IS NULL AND SYSDATE > el.enddate THEN '超期'
                                            WHEN el.releasesealdate IS NULL AND SYSDATE > el.reminderdate THEN '预超期'
                                       ELSE '油封期中' END status
                                FROM enginepartoilseal el
                                LEFT JOIN Sessionvalues sv ON sv.employeeid = el.employeeid
                                LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
                                LEFT JOIN (
                                            SELECT el.engineno,el.productid,el.partid,COUNT(1) yfCount,MAX(el.createdate) maxCreatadate
                                            FROM enginepartoilseal el
                                            GROUP BY el.engineno,el.productid,el.partid
                                          ) m ON nvl(m.productid,1) = nvl(el.productid,1) AND m.engineno = el.engineno AND nvl(m.partid,2) = nvl(el.partid,2)
                                WHERE el.isuse = 0
                            ) t
                            GROUP BY t.workcentername

                        ) m ON m.workcentername = w.workcentername
                        WHERE w.wctype = 1 ";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND w.factoryid = '{0}' ",para["factoryid"].ToString());
            }

            DataTable dtResult = dal.GetDataTable(sql);

            dtResult.Columns.Add("title");

            foreach (DataRow dr in dtResult.Rows)
            {
                if (dr["workcentername"].ToString().Contains("装试"))
                {
                    dr["title"] = dr["workcentername"].ToString().Replace("装试", "");
                }
            }

            return dtResult;
        }

        //油封预警明细
        public DataTable GetOilDetailsInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
                                     SELECT t.*
                                     FROM
                                     (
                                         SELECT w.workcentername,
                                                CASE WHEN el.containerno IS NULL AND el.partid IS NULL THEN '整机油封'
                                                     WHEN el.containerno IS NOT NULL AND el.productid IS NULL THEN '部件油封'
                                                     WHEN el.productid IS NOT NULL AND el.containerno IS NOT NULL THEN '组件油封' END oilType,
                                                       
                                                CASE WHEN m.yfCount > 1 AND el.createdate < m.maxCreatadate THEN '已再次油封'
                                                     WHEN el.releasesealdate IS NOT NULL THEN '解封'
                                                     WHEN el.releasesealdate IS NULL AND SYSDATE > el.enddate THEN '超期'
                                                     WHEN el.releasesealdate IS NULL AND SYSDATE > el.reminderdate THEN '预超期'
                                                     ELSE '油封期中' END status,
                                                el.engineno,el.startdate,el.enddate,lc.locationname,os.oilsealcycle,e.fullname,el.qty,
                                                NVL(pb2.productname,pb.productname) productname,NVL(p2.description,p.description) description,
                                                el.productbatchno,el.isjz,el.type,el.notes
                                         FROM enginepartoilseal el
                                         LEFT JOIN LOCATION lc ON lc.locationid = el.locationid
                                         LEFT JOIN oilsealcycleinfo os ON os.oilsealcycleinfoid = el.oilsealcycleinfoid
                                         LEFT JOIN employee e ON e.employeeid = el.employeeid
                                         LEFT JOIN product p ON p.productid = el.productid --零件件号
                                         LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                                         LEFT JOIN product p2 ON p2.productid = el.partid --部件件号
                                         LEFT JOIN productbase pb2 ON pb2.productbaseid = p2.productbaseid
                                         LEFT JOIN Sessionvalues sv ON sv.employeeid = el.employeeid
                                         LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
                                         LEFT JOIN
                                         (
                                              SELECT el.engineno,el.productid,el.partid,COUNT(1) yfCount,MAX(el.createdate) maxCreatadate
                                              FROM enginepartoilseal el
                                              GROUP BY el.engineno,el.productid,el.partid
                                         ) m ON nvl(m.productid,1) = nvl(el.productid,1) AND m.engineno = el.engineno AND nvl(m.partid,2) = nvl(el.partid,2)
                                         WHERE el.isuse = 0";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND w.workcentername='{0}' ", para["workcentername"].ToString());
            }

            sql += " ) t WHERE 1=1 ";
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "预超期")
            {
                sql += " AND t.status = '预超期'";
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "超期")
            {
                sql += " AND t.status = '超期'";
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //设备预警
        public DataTable GetToolsInfo(Dictionary<string,object> para)
        {
            string sql = @"
SELECT w.workcentername,nvl(m.delay,0) delay,nvl(m.ycq,0) ycq
FROM Factory f
LEFT JOIN workcenter w ON w.factoryid = f.factoryid
LEFT JOIN
(
      SELECT t.workcentername,SUM(CASE WHEN t.stauts = '超期' THEN 1 ELSE 0 END) delay,SUM(CASE WHEN t.stauts = '预超期' THEN 1 ELSE 0 END) ycq
      FROM 
      (
        SELECT rci.workcentername,
               CASE WHEN to_date(to_char(rci.zjdrq,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE) >= 0 
                         AND to_date(to_char(rci.zjdrq,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE) <= 31 THEN '预超期'
                    WHEN to_date(to_char(rci.zjdrq,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE) < 0 THEN '超期' END stauts
        FROM t_Resourceregularcheckinfo rci
        WHERE rci.zjdrq IS NOT NULL
      ) t
      GROUP BY t.workcentername
) m ON m.workcentername = w.workcentername
WHERE w.wctype = 1 ";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            sql += "ORDER BY w.workcentername";

            DataTable dtResult = dal.GetDataTable(sql);

            dtResult.Columns.Add("title");

            foreach (DataRow dr in dtResult.Rows)
            {
                if (dr["workcentername"].ToString().Contains("装试"))
                {
                    dr["title"] = dr["workcentername"].ToString().Replace("装试", "");
                }
            }

            return dtResult;
        }

        //设备预警明细信息
        public DataTable GetToolsDetailsInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"SELECT rci.* FROM t_Resourceregularcheckinfo rci WHERE rci.zjdrq IS NOT NULL ";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND rci.workcentername='{0}' ",para["workcentername"].ToString());
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "预超期")
            {
                sql += " AND to_date(to_char(rci.zjdrq,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE) >= 0 AND to_date(to_char(rci.zjdrq,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE) <= 31";
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "超期")
            {
                sql += " AND to_date(to_char(rci.zjdrq,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE) < 0";
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //工装工具 modified by zhunan 2020.3.25
        public DataTable GetToolPrepInfo()
        {
            //AND rc.kostl = 'LYDL0037' AND rc.chktyp IN ('3','4','03','04','3,') 2019.12.11按照刘飞要求新增
            string sql = @"
SELECT t.zxhao,COUNT(t.zxhao) total,NVL(SUM(CASE WHEN t.status = '预警期' THEN 1 ELSE 0 END),0) ycq,
       NVL(SUM(CASE WHEN t.status = '已超期' THEN 1 ELSE 0 END),0) delay
FROM (
    SELECT rc.zxhao,rc.vldat,rc.zjdzq,CASE WHEN trunc(rc.vldat + rc.zjdzq) <= TRUNC(SYSDATE) THEN '已超期' ELSE '预警期' END status
    FROM t_toolregularcheckinfo rc
    WHERE rc.vldat IS NOT NULL AND rc.zxhao IS NOT NULL AND rc.kostl = 'LYDL0037' AND rc.chktyp IN ('3','4','03','04','3,')
          AND rc.zjdzq IS NOT NULL AND trunc(rc.vldat + rc.zjdzq) BETWEEN TRUNC(sysdate, 'mm') AND TRUNC(last_day(sysdate)) + 3
    UNION ALL
    SELECT rc.zxhao,rc.vldat,rc.zjdzq,'已超期' status
    FROM t_toolregularcheckinfo rc
    WHERE rc.vldat IS NOT NULL AND rc.zxhao IS NOT NULL AND rc.kostl = 'LYDL0037' AND rc.chktyp IN ('3','4','03','04','3,')
          AND rc.zjdzq IS NOT NULL AND trunc(rc.vldat + rc.zjdzq) < TRUNC(sysdate, 'mm')
) t
GROUP BY t.zxhao
";

            DataTable dtQueryData = dal.GetDataTable(sql);

            DataTable dtSplit = new DataTable();
            dtSplit.Columns.Add("ZXHAO", typeof(string));
            dtSplit.Columns.Add("DELAY", typeof(Int32));
            dtSplit.Columns.Add("YCQ", typeof(Int32));

            foreach (DataRow dr in dtQueryData.Rows)
            {
                if (dr["zxhao"].ToString().IndexOf('、') != -1)
                {
                    string[] stringPFList = dr["zxhao"].ToString().Split('、');
                    for (int i = 0; i < stringPFList.Length; i++)
                    {
                        DataRow newRow = dtSplit.NewRow();
                        newRow["ZXHAO"] = stringPFList[i];
                        newRow["DELAY"] = dr["delay"];
                        newRow["YCQ"] = dr["ycq"];
                        dtSplit.Rows.Add(newRow);
                    }
                }
                else
                {
                    DataRow newRow = dtSplit.NewRow();
                    newRow["ZXHAO"] = dr["zxhao"];
                    newRow["DELAY"] = dr["delay"];
                    newRow["YCQ"] = dr["ycq"];
                    dtSplit.Rows.Add(newRow);
                }
            }

            DataTable dtReturn = dtSplit.DefaultView.ToTable(true, "ZXHAO");

            dtReturn.Columns.Add("DELAY", typeof(Int32));
            dtReturn.Columns.Add("YCQ", typeof(Int32));
            dtReturn.Columns.Add("TOTAL", typeof(Int32));

            foreach (DataRow datarow in dtReturn.Rows)
            {
                string filter = string.Format("ZXHAO = '{0}'", datarow["ZXHAO"].ToString());
                datarow["DELAY"] = dtSplit.Compute("Sum(DELAY)", filter);
                datarow["YCQ"] = dtSplit.Compute("Sum(YCQ)", filter);
                datarow["TOTAL"] = Convert.ToInt32(datarow["DELAY"]) + Convert.ToInt32(datarow["YCQ"]);
            }

            return dtReturn;
        }

        //工装工具明细信息 modified by zhunan 2020.3.25
        public DataTable GetToolPrepDetailsInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            //AND rc.kostl = 'LYDL0037' AND rc.chktyp IN ('3','4','03','04','3,') 2019.12.11按照刘飞要求新增
            string sql = @"
SELECT t.*
FROM (
    SELECT rc.*,CASE WHEN trunc(rc.vldat + rc.zjdzq) <= TRUNC(SYSDATE) THEN '超期' ELSE '预超期' END status2
    FROM t_toolregularcheckinfo rc
    WHERE rc.vldat IS NOT NULL AND rc.zxhao IS NOT NULL AND rc.kostl = 'LYDL0037' AND rc.chktyp IN ('3','4','03','04','3,')
          AND rc.zjdzq IS NOT NULL AND trunc(rc.vldat + rc.zjdzq) BETWEEN TRUNC(sysdate, 'mm') AND TRUNC(last_day(sysdate)) + 3
    UNION ALL
    SELECT rc.*,'已超期' status2
    FROM t_toolregularcheckinfo rc
    WHERE rc.vldat IS NOT NULL AND rc.zxhao IS NOT NULL AND rc.kostl = 'LYDL0037' AND rc.chktyp IN ('3','4','03','04','3,')
          AND rc.zjdzq IS NOT NULL AND trunc(rc.vldat + rc.zjdzq) < TRUNC(sysdate, 'mm')
) t
WHERE 1=1 
";
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND t.zxhao LIKE '%{0}%' ", para["productfamilyname"].ToString());
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "预超期")
            {
                sql += " AND t.status2 = '预超期'";
            }
            if (para.ContainsKey("queryType") && para["queryType"].ToString() == "超期")
            {
                sql += " AND t.status2 = '超期'";
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //工艺准备 add zhunan 2020.10.14
        public DataTable GetGongYiZhunBei()
        {
            string sql = @"
SELECT replace(w.workcentername,'装试','') workcentername,
       count(et.enginetechstatusid) planQty,NVL(SUM(CASE WHEN et.status = '已完成' THEN 1 ELSE 0 END),0) finishQty,
       count(et.enginetechstatusid) - NVL(SUM(CASE WHEN et.status = '已完成' THEN 1 ELSE 0 END),0) notfinishqty
FROM enginetechstatus et
LEFT JOIN workcenter w ON w.workcenterid = et.workcenterid
LEFT JOIN factory f ON f.factoryid = w.factoryid
WHERE f.factoryname='发动机总体事业部' AND trunc(et.createtime) BETWEEN TRUNC(sysdate, 'mm') AND TRUNC(last_day(sysdate))
GROUP BY w.workcentername
";
            return dal.GetDataTable(sql);
        }

        //工艺文件准备 add zhunan 2020.10.15
        public DataTable GetGongYiZhunBeiDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT et.engineno,pb.productname,p.description,et.workflowstatus,e.fullname,et.createtime,replace(w.workcentername,'装试','') workcentername,et.status
FROM enginetechstatus et
LEFT JOIN workcenter w ON w.workcenterid = et.workcenterid
LEFT JOIN factory f ON f.factoryid = w.factoryid
LEFT JOIN product p ON p.productid = et.productid
LEFT JOIN employee e ON e.employeeid = et.createemployeeid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
WHERE f.factoryname='发动机总体事业部' AND trunc(et.createtime) BETWEEN TRUNC(sysdate, 'mm') AND TRUNC(last_day(sysdate))
";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND w.workcentername like '%{0}%' ", para["workcentername"].ToString());
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //今日人员齐备 add zhunan 2020.10.14
        public DataTable GetRenYuanQiBei()
        {
            string sql = @"
SELECT replace(w.workcentername,'装试','') workcentername,wm.planvalue,nvl(wmd.actualvalue,0) actualvalue,wm.planvalue - nvl(wmd.actualvalue,0) queqinvalue
FROM sqcdp_factory_maint fm
LEFT JOIN sqcdp_workcenter_maint wm ON wm.sqcdp_factory_maintid = fm.sqcdp_factory_maintid
LEFT JOIN sqcdp_workcenter_maint_details wmd ON wmd.sqcdp_workcenter_maintid = wm.sqcdp_workcenter_maintid
LEFT JOIN Workcenter w ON w.workcenterid = wm.workcenterid
LEFT JOIN factory f ON f.factoryid = fm.factoryid
WHERE f.factoryname = '发动机总体事业部' AND fm.sqcdptype = 3 AND fm.itemname = '人员齐备' AND fm.itemname = '人员齐备'
      AND trunc(wmd.createdate) = TRUNC(SYSDATE)
";
            return dal.GetDataTable(sql);
        }

        //今日人员齐备明细 add zhunan 2020.10.15
        public DataTable GetRenYuanQiBeiDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT replace(w.workcentername,'装试','') workcentername,wm.planvalue,nvl(wmd.actualvalue,0) actualvalue,
       wm.planvalue - nvl(wmd.actualvalue,0) queqinvalue,wmd.createdate,e.fullname
FROM sqcdp_factory_maint fm
LEFT JOIN sqcdp_workcenter_maint wm ON wm.sqcdp_factory_maintid = fm.sqcdp_factory_maintid
LEFT JOIN sqcdp_workcenter_maint_details wmd ON wmd.sqcdp_workcenter_maintid = wm.sqcdp_workcenter_maintid
LEFT JOIN Workcenter w ON w.workcenterid = wm.workcenterid
LEFT JOIN factory f ON f.factoryid = fm.factoryid
LEFT JOIN employee e ON e.employeeid = wmd.createemployeeid
WHERE f.factoryname = '发动机总体事业部' AND fm.sqcdptype = 3 AND fm.itemname = '人员齐备' AND fm.itemname = '人员齐备'
      AND trunc(wmd.createdate) = TRUNC(SYSDATE)
";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND w.workcentername like '%{0}%' ", para["workcentername"].ToString());
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

    }
}
